package com.ruoyi.customer.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.core.mapper.BaseMapperPlus;
import com.ruoyi.customer.domain.Customer;
import com.ruoyi.customer.domain.UserCustomerCount;
import com.ruoyi.customer.domain.vo.CustomerVo;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * 客户信息Mapper接口
 *
 * @author suntf
 * @date 2023-07-29
 */
public interface CustomerMapper extends BaseMapperPlus<CustomerMapper, Customer, CustomerVo> {


    @Insert("<script>" +
            " insert into t_customer (name, tel, sex, level, bz, customer_status, company_status, distribute_dept_id, source, data_source, create_time,create_by, total_customer_id, batch_logo) "
            +
            " select a.name, a.mobile, a.sex , a.level, remark, a.customer_status, 0, 100, '0', 0, now(), #{userId}, a.id, batch_logo" +
            "   from total_comster a " +
            "  where " +
            "    a.id = #{id} " +
            "    and a.del_flag  = 0 " +
            "    and not exists(select 1 from t_customer b where a.id = b.total_customer_id and b.deleted = 0)" +
            "</script>")
    Integer transformerCustomer(@Param("id") Long id, @Param("userId") String userId);


    @Insert("<script>" +
            " insert into t_customer (name, tel, sex, level, bz, company_status, distribute_dept_id, source, data_source, create_time,create_by, total_customer_id, batch_logo) "
            +
            " select a.name, a.mobile, a.sex , a.level, remark, 0, 100, '0', 0, now(), #{createBy}, a.id, batch_logo" +
            "   from total_comster a " +
            "  where " +
            "    a.mobile = #{tel} " +
            "    and not exists(select 1 from t_customer b where a.mobile = b.tel and b.deleted = 0)" +
            "</script>")
    Integer oneKeyIssueCustomer(@Param("tel") String tel, @Param("createBy") String createBy);

    Page<CustomerVo> selectPageCustomerList(@Param("page") Page<CustomerVo> page,
            @Param(Constants.WRAPPER) Wrapper<Customer> queryWrapper);

    List<CustomerVo> selectCustomerList(@Param(Constants.WRAPPER) Wrapper<Customer> queryWrapper);

    /**
     * type = 1  1星(待签约状态) 七天提醒 type = 2  2、4、5、6星三天一提醒， type = 3  3星 1天提醒
     *
     * @param userId
     * @param type
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     * @author: suntf
     * @date: 2023/8/16 20:38
     */
    @Select("<script>" +
            "select a.id, a.tel, a.name, a.`level`, a.task_id user_id, a.distribute_dept_id dept_id, a.task_id " +
            "  from t_customer a, sys_user u " +
            " where a.deleted  = 0  " +
            " and a.task_id = u.user_id and u.locked = 0" +
            "  and a.task_id != -1 " +
            "  and a.is_reviewe = -1 " +
            " <choose>" +
            "    <when test='lookType == \"team\" '>" +
            "      and (a.distribute_dept_id like concat(#{deptId},'%') or a.task_id = #{userId} ) " +
            "    </when>" +
            "    <otherwise>" +
            "       <if test=\"userId !=null and userId !='' \"> " +
            "           and  a.task_id  = #{userId} " +
            "       </if>" +
            "    </otherwise>" +
            " </choose> " +
            "<if test='type == \"one\" '>" +
            " and TIMESTAMPDIFF(MINUTE, ifnull(a.last_remark_time,'2000-12-31 00:00:00'), now()) > 7 * 24 * 60 and a.customer_status = 3 and a.`level` = 1 "
            +
            "</if>" +
            "<if test='type == \"other\" '>" +
            " <![CDATA[ and TIMESTAMPDIFF(MINUTE, ifnull(a.last_remark_time,'2000-12-31 00:00:00'), now()) > 3 * 24 * 60 and a.`level` in (2,4,5,6) and a.customer_status = 3 ]]>"
            +
            "</if>" +
            "<if test='type == \"three\" '>" +
            " <![CDATA[ and TIMESTAMPDIFF(MINUTE, ifnull(a.last_remark_time,'2000-12-31 00:00:00'), now()) > 1 * 24 * 60 and a.`level` = 3 and a.customer_status = 3 ]]> "
            +
            "</if>" +
            "<if test='type == \"newMyCustomer\" '>" +
            " and a.customer_status = 1  and a.company_status != 3 " +
            "</if>" +
            "<if test='type == \"newAgainCustomer\" '>" +
            " and a.customer_status = 1 and a.company_status = 3" +
            "</if>" +
            "<if test=\"newTerm != null and newTerm != '' \">" +
            " and a.is_new = 1" +
            "</if>" +
            "</script>")
    List<Map<String, String>> getUserStar(@Param("deptId") String deptId, @Param("userId") String userId,
            @Param("type") String type, @Param("lookType") String lookType, @Param("newTerm") String newTerm);



    /**
     * lookType 等于团队会少数据 查不到部门100的数据  不是当前本人
     * type = 1  1星(待签约状态) 七天提醒 type = 2  2、4、5、6星三天一提醒， type = 3  3星 1天提醒
     *
     * @param userId
     * @param type
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     * @author: suntf
     * @date: 2023/8/16 20:38
     */
    @Select("<script>" +
            "select a.id, a.tel, a.name, a.`level`, a.task_id user_id, a.distribute_dept_id dept_id, a.task_id " +
            "  from t_customer a, sys_user u " +
            " where a.deleted  = 0  " +
            " and a.task_id = u.user_id and u.locked = 0" +
            "  and a.task_id != -1 " +
            "  and a.is_reviewe = -1 " +
            " <choose>" +
            "    <when test='lookType == \"team\" '>" +
            "      and (a.distribute_dept_id like concat(#{deptId},'%') or a.task_id = #{userId} ) " +
            "    </when>" +
            "    <otherwise>" +
            "       <if test=\"userId !=null and userId !='' \"> " +
            "           and  a.task_id  = #{userId} " +
            "       </if>" +
            "    </otherwise>" +
            " </choose> " +
            "<if test='type == \"one\" '>" +
            " and DATEDIFF(DATE_FORMAT(now(), '%Y-%m-%d'), DATE_FORMAT(ifnull(a.last_remark_time,'2000-12-31 00:00:00'), '%Y-%m-%d')) >= 7  and a.customer_status = 3 and a.`level` = 1 "
            +
            "</if>" +
            "<if test='type == \"other\" '>" +
            " <![CDATA[ and DATEDIFF(DATE_FORMAT(now(), '%Y-%m-%d'), DATE_FORMAT(ifnull(a.last_remark_time,'2000-12-31 00:00:00'), '%Y-%m-%d')) >= 3  and a.`level` in (2,4,5,6) and a.customer_status = 3 ]]>"
            +
            "</if>" +
            "<if test='type == \"three\" '>" +
            " <![CDATA[ and DATEDIFF(DATE_FORMAT(now(), '%Y-%m-%d'), DATE_FORMAT(ifnull(a.last_remark_time,'2000-12-31 00:00:00'), '%Y-%m-%d')) >= 1 and a.`level` = 3 and a.customer_status = 3 ]]> "
            +
            "</if>" +
            "<if test='type == \"newMyCustomer\" '>" +
            " and a.customer_status = 1  and a.company_status != 3 " +
            "</if>" +
            "<if test='type == \"newAgainCustomer\" '>" +
            " and a.customer_status = 1 and a.company_status = 3" +
            "</if>" +
            "<if test=\"newTerm != null and newTerm != '' \">" +
            " and a.is_new = 1" +
            "</if>" +
            "</script>")
    List<Map<String, String>> getUserStarSend(@Param("deptId") String deptId, @Param("userId") String userId,
                                          @Param("type") String type, @Param("lookType") String lookType, @Param("newTerm") String newTerm);


//
//    /**
//     * 用户列表提醒
//     */
//    @Select("<script>" +
//            "select a.id, a.tel, a.name, a.`level`, a.task_id user_id, a.distribute_dept_id dept_id, a.task_id " +
//            "  from t_customer a, sys_user u " +
//            " where a.deleted  = 0  " +
//            " and a.task_id = u.user_id and u.locked = 0" +
//            "  and a.is_reviewe = -1 " +
//            "   and a.task_id  != -1 " +
//            " <choose>" +
//            "    <when test='lookType == \"team\" '>" +
//            "      and (exists(select 1 from sys_dept c where c.ancestors like concat(#{deptId},'%') and c.dept_id = a.distribute_dept_id and c.del_flag = 0) or a.task_id = #{userId}) "
//            +
//            "    </when>" +
//            "    <otherwise>" +
//            "       <if test=\"userId !=null and userId !='' \"> " +
//            "           and a.task_id = #{userId} " +
//            "       </if>" +
//            "    </otherwise>" +
//            " </choose> " +
//            "<if test='type == \"one\" '>" +
//            " and TIMESTAMPDIFF(MINUTE, ifnull(a.last_remark_time,'2000-12-31 00:00:00'), now()) > 7 * 24 * 60 and a.customer_status = 3 and a.`level` = 1 "
//            +
//            "</if>" +
//            "<if test='type == \"other\" '>" +
//            " <![CDATA[ and TIMESTAMPDIFF(MINUTE, ifnull(a.last_remark_time,'2000-12-31 00:00:00'), now()) > 3 * 24 * 60 and a.`level` in (2,4,5,6) and a.customer_status = 3 ]]>"
//            +
//            "</if>" +
//            "<if test='type == \"three\" '>" +
//            " <![CDATA[ and TIMESTAMPDIFF(MINUTE, ifnull(a.last_remark_time,'2000-12-31 00:00:00'), now()) > 1 * 24 * 60 and a.`level` = 3 and a.customer_status = 3 ]]> "
//            +
//            "</if>" +
//            "<if test='type == \"new\" '>" +
//            " and a.customer_status = 1 and a.is_new = 1 " +
//            "</if>" +
//            "<if test='lookType == \"person\" '>" +
//            " and a.company_status != 3 " +
//            "</if>" +
//            "<if test='lookType == \"reDis\" '>" +
//            " and a.company_status = 3 " +
//            "</if>" +
//            "</script>")
//    List<Map<String, String>> getUserListStar(@Param("deptId") String deptId, @Param("userId") String userId,
//            @Param("type") String type, @Param("lookType") String lookType);


    /**
     * 用户列表提醒
     */
    @Select("<script>" +
            "select a.id, a.tel, a.name, a.`level`, a.task_id user_id, a.distribute_dept_id dept_id, a.task_id " +
            "  from t_customer a, sys_user u " +
            " where a.deleted  = 0  " +
            " and a.task_id = u.user_id and u.locked = 0" +
            "  and a.is_reviewe = -1 " +
            "   and a.task_id  != -1 " +
            " <choose>" +
            "    <when test='lookType == \"team\" '>" +
            "      and (exists(select 1 from sys_dept c where c.ancestors like concat(#{ancestors},'%') and c.dept_id = a.distribute_dept_id and c.del_flag = 0) or a.task_id = #{userId} or a.distribute_dept_id = #{deptId}) "
            +
            "    </when>" +
            "    <otherwise>" +
            "       <if test=\"userId !=null and userId !='' \"> " +
            "           and a.task_id = #{userId} " +
            "       </if>" +
            "    </otherwise>" +
            " </choose> " +
            "<if test='type == \"one\" '>" +
            " and DATEDIFF(DATE_FORMAT(now(), '%Y-%m-%d'), DATE_FORMAT(ifnull(a.last_remark_time,'2000-12-31 00:00:00'), '%Y-%m-%d')) >= 7 and a.customer_status = 3 and a.`level` = 1 "
            +
            "</if>" +
            "<if test='type == \"other\" '>" +
            " <![CDATA[ and DATEDIFF(DATE_FORMAT(now(), '%Y-%m-%d'), DATE_FORMAT(ifnull(a.last_remark_time,'2000-12-31 00:00:00'), '%Y-%m-%d')) >= 3 and a.`level` in (2,4,5,6) and a.customer_status = 3 ]]>"
            +
            "</if>" +
            "<if test='type == \"three\" '>" +
            " <![CDATA[ and DATEDIFF(DATE_FORMAT(now(), '%Y-%m-%d'), DATE_FORMAT(ifnull(a.last_remark_time,'2000-12-31 00:00:00'), '%Y-%m-%d')) >= 1 and a.`level` = 3 and a.customer_status = 3 ]]> "
            +
            "</if>" +
            "<if test='type == \"new\" '>" +
            " and a.customer_status = 1 " +    //AND a.is_new = 1
            "</if>" +
            "<if test='lookType == \"person\" '>" +
            " and a.company_status != 3 " +
            "</if>" +
            "<if test='lookType == \"reDis\" '>" +
            " and a.company_status = 3 " +
            "</if>" +
            "</script>")
    List<Map<String, String>> getUserListStarChange(@Param("deptId") String deptId, @Param("ancestors") String ancestors, @Param("userId") String userId,
                                              @Param("type") String type, @Param("lookType") String lookType);

    @Insert("<script> " +
            " insert into t_recover_customer_log (user_id,user_dept,customer_id,customer_name,customer_tel,`level`,task_id,operator_day) values "
            +
            "  <foreach collection='lists' item='list' separator=',' > " +
            "  (#{list.user_id}, #{list.dept_id}, #{list.id}, #{list.name}, #{list.tel}, #{list.level}, #{list.task_id}, #{today} ) "
            +
            "  </foreach>" +
            " </script>")
    Integer saveRecoveryCustomer(@Param("lists") List<Map<String, String>> list, @Param("today") String today);

    @Select("select  * from t_recover_customer_log where operator_day = #{today}")
    Object getInfo(@Param("today") String today);

    @Delete("delete  from t_recover_customer_log where operator_day  = #{day}")
    void deleteRecoveryByDay(@Param("day") String today);

    @Delete("delete a from t_recover_customer_log a where exists(select 1 from sys_user b where a.user_id = b.user_id and b.locked = 1) and a.operator_day  = #{day}")
    void deleteRecoveryByLockedUser(@Param("day") String today);

    @Update("update t_recover_customer_log a, t_customer_task b set b.del_flag  = 1 " +
            " where a.task_id = b.id  and b.del_flag  = 0 and a.operator_day  = #{today}")
    Integer updateTask(@Param("today") String today);

    @Update("update t_recover_customer_log a, t_customer b " +
            "  set b.distribute_dept_id = #{dept_id}, b.task_id  = -1, company_status = 6, is_push = '0'"
            +
            " where a.customer_id  = b.id and a.task_id  = b.task_id and a.operator_day = #{today} and b.customer_status = 3 ")
    Integer updateCustomer(@Param("today") String today, @Param("dept_id") String dept_id);


    @Select("select a.id from t_customer a, ${tableName} b  where a.tel = b.tel and b.id = #{id} and a.deleted = 0")
    Map<String, String> getCustomerId(@Param("id") Long id, @Param("tableName") String tableName);

    @Update("update t_customer a set a.deleted  = 1,  update_time  = now(), update_by  = #{updateBy} where a.id = #{id} and a.deleted  = 0")
    Integer deleteReturnUser(@Param("updateBy") String updateBy, @Param("id") Long id);

    @Insert("<script>" +
            "INSERT into total_comster (name, mobile, remark, sex, import_time, final_follow_time, create_by, create_time, del_flag, customer_flag, source) " +
            "SELECT a.name, a.tel, a.bz, a.sex, a.create_time, a.last_remark_time, a.create_by, a.create_time, '0', '1', '新增客户' " +
            "from t_customer a " +
            "where a.id = #{id} " +
            "and not exists(select 1 from total_comster b where a.tel = b.mobile and b.del_flag = 0) " +
            "</script>")
    Integer customerInsert(@Param("id") Long id);


    @Update("update t_customer a, total_comster b set a.total_customer_id = b.id, b.customer_flag = '1', b.del_flag = '0' where a.deleted = 0 and a.tel = b.mobile and a.id = #{id} ")
    Integer customerUpdateTotalId(@Param("id") Long id);

    List<SysUser> deptByUser(@Param("roleKey") String roleKey, @Param("deptIds") List<String> deptIds);

    // 查询当前用户信息
    @Select("SELECT count(1) FROM t_customer where deleted = 0 and task_id = #{userId}")
    Integer getUserCustomerCount(@Param("userId") String userId);


    @Select("SELECT u.user_id userId, u.nick_name busName, (SELECT count(1) from t_customer a where a.company_status != 3 and a.task_id = u.user_id and a.deleted = 0 ) countSum \n"
            + "FROM sys_user u where u.user_id IN ( ${userId} ) ")
    List<UserCustomerCount> getUserCount(@Param("userId") String userId);

    @Update("update t_customer b " +
            "  set b.distribute_dept_id = #{dept_id} "
            +
            " where b.task_id  =  #{userId}")
    Integer updateCustomerDeptByTask(@Param("userId") Long userId, @Param("dept_id") Long dept_id);

    /**
     * 认领数据信息
     * @return
     */
    List<Customer> newIntentionToBus();

    void saveNewIntentionRecord();

}
